﻿
Imports System.IO
Public Class Employee
    Dim pic As Integer = 0
    Private Sub Employee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Button2.Visible = False
        dtppos.Format = DateTimePickerFormat.Custom
        dtppos.CustomFormat = "dd-MMM-yyyy"
        dtprole.Format = DateTimePickerFormat.Custom
        dtprole.CustomFormat = "dd-MMM-yyyy"
        dtpdatein.Format = DateTimePickerFormat.Custom
        dtpdatein.CustomFormat = "dd-MMM-yyyy"

        txtpathp.Visible = False
        txtpathr.Visible = False
        txtpathdate.Visible = False

        cboprovince.Visible = False
        loadsex()
        loadsexkid()
        loadministry()

        loadPosi()
        loadrole()
    End Sub
    Private Sub loadprovince()
        cboprovince.ValueMember = "ProvinceId"
        cboprovince.DisplayMember = "ProvinceName"
        cboprovince.DataSource = cmd.ExcecuteDatatable("SELECT ProvinceId,ProniceName FROM Province")
    End Sub
    Private Sub loadsex()
        cbosex.ValueMember = "SexId"
        cbosex.DisplayMember = "SexName"
        cbosex.DataSource = cmdp.ExecuteDataTable("SELECT SexId,SexName FROM Sex")
    End Sub
    Private Sub loadsexkid()
        cboksex.ValueMember = "SexId"
        cboksex.DisplayMember = "SexName"
        cboksex.DataSource = cmdp.ExecuteDataTable("SELECT SexId,SexName FROM Sex")
    End Sub
    Private Sub loadministry()
        cbominis.ValueMember = "MinistryId"
        cbominis.DisplayMember = "MinistryName"
        cbominis.DataSource = cmd.ExcecuteDatatable("SELECT MinistryId,MinistryName FROM Ministry")
    End Sub
    
    Private Sub loadPosi()
        cbopos.ValueMember = "PositionId"
        cbopos.DisplayMember = "PositionName"
        cbopos.DataSource = cmd.ExcecuteDatatable("SELECT PositionId,PositionName FROM [Position]")
    End Sub
    Private Sub loadrole()
        cborole.ValueMember = "RoleId"
        cborole.DisplayMember = "RoleName"
        cborole.DataSource = cmd.ExcecuteDatatable("SELECT RoleId,RoleName FROM Role")
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ofd As New OpenFileDialog
        ofd.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*"
        If ofd.ShowDialog = Windows.Forms.DialogResult.OK Then
            Me.PictureBox1.Image = System.Drawing.Image.FromFile(ofd.FileName)
            pic = 1
            PictureBox2.Visible = False
            PictureBox1.Visible = True
        End If
    End Sub

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click

        If txtname.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលឈ្មោះមន្រ្តីឱ្យបានត្រឹមត្រូវ")
            txtname.Focus()
            Exit Sub
        End If
        If txtno.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលអត្តលេខឱ្យបានត្រឹមត្រូវ")
            txtno.Focus()
            Exit Sub
        End If
        If txtpob.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលទីកន្លែងកមណើតឱ្យបានត្រឹមត្រូវ")
            txtpob.Focus()
            Exit Sub
        End If
        If txtknowledge.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលកំរិតវប្បធម៌ឱ្យបានត្រឹមត្រូវ")
            txtknowledge.Focus()
            Exit Sub
        End If
        If txtlanguage.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលភាសាឱ្យបានត្រឹមត្រូវ")
            txtlanguage.Focus()
            Exit Sub
        End If

        If cbpos.Checked = False Then
            If txtnopos.Text.Trim = "" Then
                MessageBox.Show("សូមបញ្ចូលលេខឋានន្តរស័ក្តិឱ្យបានត្រឹមត្រូវ")
                txtnopos.Focus()
                Exit Sub
            End If
        End If
        If cbrol.Checked = False Then
            If txtnorole.Text.Trim = "" Then
                MessageBox.Show("សូមបញ្ចូលលេខតួនាទីឱ្យបានត្រឹមត្រូវ")
                txtnorole.Focus()
                Exit Sub
            End If
        End If
        
        If txtcheck.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលលេខបណ្ណ័ត្រួតពិនិត្រឱ្យបានត្រឹមត្រូវ")
            txtcheck.Focus()
            Exit Sub
        End If
        If txttransfer.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលទិន្ន័យមន្រ្តីផ្ទេរមកពីឱ្យបានត្រឹមត្រូវ")
            txttransfer.Focus()
            Exit Sub
        End If
        If txtaddress.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលអាសយដ្ឋានបច្ចុប្បន្នរបស់មន្រ្តីឱ្យបានត្រឹមត្រូវ")
            txtaddress.Focus()
            Exit Sub
        End If
        If IsInteger(Me.txtno.Text) = False Then
            MsgBox("សូមបញ្ចូលអត្តលេខជាលេងអន្តរជាតិ")
            txtno.SelectAll()
            txtno.Focus()
            Exit Sub
        End If

        cmdp.Parameters("@no") = txtno.Text
        Dim count As Int16 = cmdp.ExecuteScalar("SELECT COUNT(*) FROM Employee WHERE EmpNo=@no")
        'MessageBox.Show(count)
        'Exit Sub
        If count > 0 Then
            MsgBox("អត្តលេខនេះមានរួចហើយ")
            txtno.SelectAll()
            txtno.Focus()
            Exit Sub
        End If

        Dim fp = ".\pdf\" & txtno.Text & "ឋានៈ.pdf"
        Dim fp1 = ".\pdf\" & txtno.Text & "តួនាទី.pdf"
        Dim fp2 = ".\pdf\" & txtno.Text & "ថ្ងៃចូល.pdf"
        Try
            If txtpathp.Text <> "" Then
                My.Computer.FileSystem.CopyFile(txtpathp.Text, fp, True)
            Else
                fp = ""
            End If

            If txtpathr.Text <> "" Then
                My.Computer.FileSystem.CopyFile(txtpathr.Text, fp1, True)
            Else
                fp1 = ""
            End If

            If txtpathdate.Text <> "" Then
                My.Computer.FileSystem.CopyFile(txtpathdate.Text, fp2, True)
            Else
                fp2 = ""
            End If
        Catch ex As Exception
            MessageBox.Show("Save Pdf File fail")
        End Try
        
        Dim cbdate As String

        Try
            cmdp.Begin()
            cmdp.Parameters("@empno") = txtno.Text
            cmdp.Parameters("@name") = txtname.Text
            cmdp.Parameters("@@") = cbosex.SelectedValue
            cmdp.Parameters("@dob") = dtpdob.Value
            cmdp.Parameters("@mini") = cbominis.SelectedValue
            cmdp.Parameters("@pob") = txtpob.Text
            cmdp.Parameters("@phone") = txtphone.Text
            cmdp.Parameters("@know") = txtknowledge.Text
            cmdp.Parameters("@flang") = txtlanguage.Text
            cmdp.Parameters("@pos") = cbopos.SelectedValue

            If cbpos.Checked Then
                cmdp.Parameters("@pno") = Nothing
                cmdp.Parameters("@pdate") = Nothing
            Else
                cmdp.Parameters("@pno") = txtnopos.Text
                cmdp.Parameters("@pdate") = dtppos.Value
            End If

            cmdp.Parameters("@role") = cborole.SelectedValue

            If cbrol.Checked Then
                cmdp.Parameters("@rno") = Nothing
                cmdp.Parameters("@rdate") = Nothing
            Else
                cmdp.Parameters("@rno") = txtnorole.Text
                cmdp.Parameters("@rdate") = dtprole.Value
            End If

            cmdp.Parameters("@check") = txtcheck.Text
            If cbdatein.Checked Then
                cbdate = Nothing
            Else
                cbdate = dtpdatein.Value
            End If

            cmdp.Parameters("@datein") = cbdate
            cmdp.Parameters("@transfer") = txttransfer.Text
            cmdp.Parameters("@adr") = txtaddress.Text
            cmdp.Parameters("@detail") = txtdetail.Text
            cmdp.Parameters("@pospdf") = fp
            cmdp.Parameters("@rolepdf") = fp1
            cmdp.Parameters("@datepdf") = fp2
            cmdp.Parameters("@fault") = txtfault.Text

            Dim MemStream As New MemoryStream
            Dim DataPic_Update As Byte()
            If pic = 1 Then
                Me.PictureBox1.Image.Save(MemStream, Imaging.ImageFormat.Png)
            Else
                Me.PictureBox2.Image.Save(MemStream, Imaging.ImageFormat.Png)
            End If
            'Me.PictureBox1.Image.Save(MemStream, Imaging.ImageFormat.Png)
            DataPic_Update = MemStream.GetBuffer()
            MemStream.Read(DataPic_Update, 0, MemStream.Length)
            cmdp.Parameters("@EmpPic") = DataPic_Update

            cmdp.ExecuteNonQuery("insert into Employee (EmpNo,EmpName,SexID,EmpDOB,MinistryId,EmpPOB,EmpPhone,EmpKnow,EmpFLang,PositionId,Pno,Pdate,RoleId,Rno,RDate,CheckNo,DateIn,Transfer,Address,Detail,PosPdf,RolePdf,dateinpdf,Fault,WorkRetire,Active,Photo) values(@empno,@name,@sex,@dob,@mini,@pob,@phone,@know,@flang,@pos,@pno,@pdate,@role,@rno,@rdate,@check,@datein,@transfer,@adr,@detail,@pospdf,@rolepdf,@datepdf,@fault,1,1,@EmpPic)")
            If DataGridView1.Rows.Count > 0 Then
                Dim empId = cmdp.ExecuteScalar("SELECT MAX(EmpId) FROM Employee")
                'MessageBox.Show(empId)
                For Each row As DataGridViewRow In Me.DataGridView1.Rows
                    Dim dob = CDate(row.Cells("rdob").Value)
                    cmdp.Parameters("@empid") = empId
                    cmdp.Parameters("@rname") = row.Cells("rname").Value
                    cmdp.Parameters("@dob") = dob
                    cmdp.Parameters("@type") = row.Cells("rtype").Value
                    cmdp.Parameters("@rsex") = row.Cells("sexid").Value

                    'MessageBox.Show(row.Cells("kname").Value & "-" & row.Cells("sexid").Value & "-" & dob)
                    'cmdp.ExecuteNonQuery("INSERT INTO [Child] (EmpId,ChildName,ChildDOB,SexId,Active) VALUES(@empid,@kname,@dob,@ksex,1);")
                    cmdp.ExecuteNonQuery("INSERT INTO [Relative] (EmpId,RelateName,RelateDOB,TypeId,SexId,Active) VALUES(@empid,@rname,@dob,@type,@rsex,1);")
                Next
            End If



            MessageBox.Show("បញ្ចូលមន្រ្តីថ្មីបានជោកជ័យ")
            pic = 0
            PictureBox2.Visible = True
            PictureBox1.Visible = False
            Me.DialogResult = Windows.Forms.DialogResult.OK
            cleartxt()
            cmdp.Commit()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            cmdp.Rollback()
        End Try

    End Sub
    Private Sub cleartxt()
        txtaddress.Clear()
        txtaddress.Clear()
        txtcheck.Clear()
        txtdetail.Clear()
        txtknowledge.Clear()
        txtlanguage.Clear()
        txtname.Clear()
        txtno.Clear()
        txtnopos.Clear()
        txtnorole.Clear()
        txtpathdate.Clear()
        txtpathp.Clear()
        txtpathr.Clear()
        txtpob.Clear()
        txtfault.Clear()
        txttransfer.Clear()
        cbominis.SelectedValue = 1
        cbopos.SelectedValue = 1
        cboprovince.SelectedValue = 1
        cborole.SelectedValue = 1
        cbosex.SelectedValue = 1
        cboksex.SelectedValue = 1
        cbpos.Checked = False
        cbrol.Checked = False
        cbdatein.Checked = False
        dtpdatein.Value = Now.Date
        dtpdob.Value = Now.Date
        dtppos.Value = Now.Date
        dtprole.Value = Now.Date
        txtkname.Text = ""
        dtpkdob.Value = Now.Date
        DataGridView1.Rows.Clear()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnp.Click
        Dim ofd As New OpenFileDialog
        ofd.Filter = "pdf|*.pdf"
        If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then
            txtpathp.Text = ofd.FileName
        End If
    End Sub

    Private Sub btnr_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnr.Click
        Dim ofd As New OpenFileDialog
        ofd.Filter = "pdf|*.pdf"
        If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then
            txtpathr.Text = ofd.FileName
        End If
    End Sub

    Private Sub btndatein_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndatein.Click
        Dim ofd As New OpenFileDialog
        ofd.Filter = "pdf|*.pdf"
        If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then
            txtpathdate.Text = ofd.FileName
        End If
    End Sub

    Friend Function IsInteger(ByVal text As String) As Boolean
        Dim tmp As Integer
        Dim res = Integer.TryParse(text, tmp)
        Return res
    End Function

    Private Sub cbpos_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbpos.CheckedChanged
        If cbpos.Checked Then
            txtnopos.Visible = False
            dtppos.Visible = False
            btnp.Visible = False
        Else
            txtnopos.Visible = True
            dtppos.Visible = True
            btnp.Visible = True
        End If
    End Sub

    Private Sub cbrol_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbrol.CheckedChanged
        If cbrol.Checked Then
            txtnorole.Visible = False
            dtprole.Visible = False
            btnr.Visible = False
        Else
            txtnorole.Visible = True
            dtprole.Visible = True
            btnr.Visible = True
        End If
    End Sub

    Private Sub cbdatein_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbdatein.CheckedChanged
        If cbdatein.Checked Then
            dtpdatein.Visible = False
            btndatein.Visible = False
        Else
            dtpdatein.Visible = True
            btndatein.Visible = True
        End If
    End Sub

    Private Sub btnaddkid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaddkid.Click
        If txtkname.Text.Trim = "" Then
            MessageBox.Show("សូមបញ្ចូលឈ្មោះកូនមន្រ្តី")
            Exit Sub
        End If
        Dim i = Me.DataGridView1.Rows.Add()
        Dim row = Me.DataGridView1.Rows(i)
        row.Cells("rname").Value = txtkname.Text
        row.Cells("rdob").Value = dtpkdob.Value
        row.Cells("rtype").Value = 2
        row.Cells("sexid").Value = cboksex.SelectedValue
        row.Cells("rsex").Value = cboksex.Text
    End Sub

    Private Sub លបToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles លបToolStripMenuItem.Click
        Dim row As DataGridViewRow
        row = DataGridView1.SelectedRows(0)
        Me.DataGridView1.Rows.Remove(row)
    End Sub

    Private Sub លបទងអសToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles លបទងអសToolStripMenuItem.Click
        'Dim row As DataGridViewRow
        'row = DataGridView1.SelectedRows(0)
        Me.DataGridView1.Rows.Clear()
    End Sub

    Private Sub DataGridView1_DoubleClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
        Dim row As DataGridViewRow
        row = DataGridView1.SelectedRows(0)
        Dim a = CDate(row.Cells("kdob").Value).ToString("MMM/dd/yyyy")
        MessageBox.Show(a)
    End Sub

    Private Sub Button2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Dim empId = cmdp.ExecuteScalar("SELECT MAX(EmpId) FROM Employee")
        ''MessageBox.Show(empId)
        'For Each row As DataGridViewRow In Me.DataGridView1.Rows
        '    cmdp.Parameters("@empid") = empId
        '    cmdp.Parameters("@kname") = row.Cells("kname").Value
        '    Dim dob = CDate(row.Cells("kdob").Value)
        '    cmdp.Parameters("@ksex") = row.Cells("sexid").Value
        '    cmdp.Parameters("@dob") = dob
        '    MessageBox.Show(row.Cells("kname").Value & "-" & row.Cells("sexid").Value & "-" & dob)
        '    'cmdp.ExecuteNonQuery("INSERT INTO [Child] (EmpId,ChildName,ChildDOB,SexId,Active) VALUES(@empid,@kname,@dob,@ksex,1);")
        'Next
        'cmdp.ExecuteNonQuery("INSERT INTO [Child] (SexId) VALUES(1)")
    End Sub
End Class